<?php
/**
 * Created by PhpStorm.
 * User: xuyiping
 * Date: 2018/7/15
 * Time: 18:26
 */

namespace App\Http\Controllers\Admin\Common;

use App\Http\Controllers\Controller;
use App\Model\GoldRecordModel;
use App\Model\PkPeopleModel;
use App\Model\PkRecordModel;
use App\Model\SignModel;
use App\Model\WebUserModel;
use Illuminate\Http\Request;
use Excel;
use DB;

class ExcelController extends Controller{

    public function index(Request $request,$op){
        $file_name = '';
        $res = [];
        switch ($op) {
            case 1: //会员管理
                $res = $this->members($request->all());
                //excel表名
                $file_name = '会员管理表';
                break;
            case 2: //数据统计
                $res = $this->statistics($request->all());
                //excel表名
                $file_name = '数据统计表';
                break;

            default;
                break;
        }

        Excel::create($file_name,function($excel) use ($res){
            $excel->sheet('score', function($sheet) use ($res){

                if (!is_null($res[1])) {
                    $sheet->setWidth($res[1]);
                }
                $sheet->rows($res[0]);

            });
        })->export('xls');
    }

    public function members($request = []){

        $where = [];

        //组装execl表头部信息
        $cellData = [['编号','注册时间','微信昵称','上次签到时间','PK人数','胜率','总场次','签到得豆数','输赢豆数','兑换豆数']];

        if($request['startDateTime'] || $request['endDateTime']){

            if($request['startDateTime']) {
                $where[] = ['dtTime', '>', $request['startDateTime']." 00:00:00"];
            }
            if($request['endDateTime']) {
                $where[] = ['dtTime', '<', $request['endDateTime']." 23:59:59"];
            }
            if ($request['name']) {
                $where[] = ['sUserName','like','%'.$request['name'].'%'];
            }

            $groups = PkRecordModel::where($where)
                ->leftJoin('webuser','webuser.iSeqId','=','pkrel.master_userid')
                ->groupBy("pkrel.master_userid")
                ->select(DB::raw('tb_pkrel.master_userid,tb_webuser.iSeqId,tb_webuser.iRegistTime,
                tb_webuser.sUserImage,tb_webuser.sUserName,tb_webuser.iAllPk'))
                ->get()
                ->toArray();

            if($groups){
                foreach ( $groups as &$row ) {
                    $signtime = SignModel::select(DB::raw('dtModifyTime'))
                        ->where(["iUserId"=>$row['iSeqId']])
                        ->orderBy("dtModifyTime","DESC")
                        ->first();
                    $res_data[] = array(
                        'id'          => $row['iSeqId'],
                        'iRegistTime' => $row['iRegistTime'],
                        'sUserImage'  => '<image src='.$row['sUserImage'].' style="width: 30px;height: 30px;"></image>',
                        'sUserName'   => $row['sUserName'],
                        'dtModifyTime'=> empty($signtime->dtModifyTime) ? 0 : $signtime->dtModifyTime,
                        'iAllPk'      => $row['iAllPk']
                    );

                    array_push( $cellData , array($row['iSeqId'] , $row['iRegistTime'] , $row['sUserName'] ,
                        $row['dtModifyTime'], $row['iAllPk'] ,0 , 0, 0 , 0 ,0 ,0));
                }
            }


        }else{

            if ($request['name']) {
                $where[] = ['sUserName','like','%'.$request['name'].'%'];
            }
            if ($request['startDate'] && $request['endDate']) {
                if($request['startDate'] <= $request['endDate']){
                    $where[] = ['iAllPk','>',$request['startDate']];
                    $where[] = ['iAllPk','<',$request['endDate']];
                }
            }
            $groups = WebUserModel::where($where)
                ->leftJoin('sign','webuser.iSeqId','=','sign.iUserId')
                ->select(DB::raw('tb_sign.dtModifyTime,tb_webuser.iSeqId,tb_webuser.iRegistTime,
            tb_webuser.sUserImage,tb_webuser.sUserName,tb_webuser.iAllPk,tb_webuser.iAll,tb_webuser.iWinAll,(tb_webuser.iWinAll/tb_webuser.iAll) as rate'))
                ->get()
                ->toArray();

            if($groups){
                foreach ( $groups as &$row ) {
                    $signgold = GoldRecordModel::select(DB::raw('sum(iGoldBeanConsume) as signgold'))
                        ->where(["iUserId"=>$row['iSeqId'],"iConsumeType"=>4])
                        ->first();
                    $exchangegold = GoldRecordModel::select(DB::raw('sum(iGoldBeanConsume) as exchangegold'))
                        ->where([ "iUserId"=>$row['iSeqId'],"iConsumeType"=>3])
                        ->first();
                    $pkgold = GoldRecordModel::select(DB::raw('sum(iGoldBeanConsume) as pkgold'))
                        ->where(["iUserId"=> $row['iSeqId']])
                        ->where("iConsumeType",'<', 3)
                        ->first();

                    $signgold     = empty($signgold->signgold) ? 0 : $signgold->signgold;
                    $exchangegold = empty($exchangegold->exchangegold) ? 0 : $exchangegold->exchangegold;
                    $pkgold       = empty($pkgold->pkgold) ? 0 : $pkgold->pkgold;
                    $rate         = empty($row['rate']) ? 0 :ceil($row['rate']*100).'%';

                    array_push( $cellData ,array($row['iSeqId'],$row['iRegistTime'],$row['sUserName'],$row['dtModifyTime'],
                        $row['iAllPk'],$rate, $row['iAll'],$signgold,$pkgold,$exchangegold));
                }
            }
        }

        $row_width = [
            'A' => 10,
            'B' => 30,
            'C' => 20,
            'D' => 30,
            'E' => 10,
            'F' => 10,
            'G' => 10,
            'H' => 20,
            'I' => 20,
            'J' => 20,
        ];

        return [$cellData, $row_width];
    }

    public function statistics($request = []){

        $start_date = $request['startDate'];
        $end_date   = $request['endDate'];

        if(!isset($start_date) || !isset($end_date)){
            $start_date = date('Y-m-d',strtotime('-10 day'));
            $end_date   = date('Y-m-d');
        }

        $res_data = array(
            'total' => 0,
            'rows' => array()
        );
        if($start_date && $end_date){
            if($end_date < $start_date){
                return response()->json($res_data);
            }
        }

        if(strtotime($start_date) == strtotime($end_date)){
            $index = 1;
        }else{
            $index = ((strtotime($end_date) - strtotime($start_date)) / 86400) +1;
        }

        $cellData = [['编号','日期','签到人数','PK人数','PK局数','净消耗豆数','新增用户','抢三玩法','抢五玩法']];


        $start_time = $start_date. ' 00:00:00';
        $end_time = $start_date. ' 23:59:59';
        $rows_array = [];
        for ($i = 0; $i < $index; $i++){

            //签到人数
            $sign_count = SignModel::where('dtModifyTime','>=',$start_time)
                ->where('dtModifyTime','<=',$end_time)
                ->count();
            //PK人数
            $pk_num = PkPeopleModel::where('dtCreateTime','>=',$start_time)
                ->where('dtCreateTime','<=',$end_time)
                ->groupBy('iUserID')
                ->count();

            //PK局数
            $pk_count = PkRecordModel::where('dtEndTime','>=',$start_time)
                ->where('dtEndTime','<=',$end_time)
                ->where('iStatus',2)
                ->count();

            //净消耗豆数
            $gold_record_count = GoldRecordModel::where('dtModifyTime','>=',$start_time)
                ->where('dtModifyTime','<=',$end_time)
                ->where('iConsumeType',2)
                ->count();
            //抢三玩法
            $pk_count_three = PkRecordModel::where('dtEndTime','>=',$start_time)
                ->where('dtEndTime','<=',$end_time)
                ->where(['iStatus'=>2,'sPkType'=>1])
                ->count();

            //新增用户数
            $user_count = WebUserModel::where('iRegistTime','>=',$start_time)
                ->where('iRegistTime','<=',$end_time)
                ->groupBy('iSeqId')->count();

            array_push( $cellData ,array($i+1,date('Y-m-d',strtotime($start_time)),$sign_count,$pk_count,
                $pk_num,$gold_record_count,$pk_count_three,$pk_count - $pk_count_three,$user_count));

            $start_time = date('Y-m-d H:i:s',strtotime($start_time)+86400);
            $end_time = date('Y-m-d H:i:s',strtotime($end_time) +86400);

        }

        $row_width = [
            'A' => 10,
            'B' => 10,
            'C' => 10,
            'D' => 10,
            'E' => 10,
            'F' => 10,
            'G' => 10,
            'H' => 10,
            'I' => 10,
        ];

        return [$cellData, $row_width];
    }

}